package com.poi.action;

import com.opensymphony.xwork2.ActionContext;
import db_tool.LogTool;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.StrutsStatics;
import tree_util.Account;

import javax.print.DocFlavor;
import javax.servlet.http.HttpServletRequest;
import java.sql.*;
import java.util.Map;

public class CatchToolsDao {
    Account account=(Account) ServletActionContext.getRequest().getSession().getAttribute("account");
	HttpServletRequest request=(HttpServletRequest)ActionContext.getContext().get(StrutsStatics.HTTP_REQUEST);

	private DbBean db = new DbBean();
	Connection conn = db.getConnection();
    private int rowCounterMonitoringSite = 0;
    private int rowCounterFractureSurface = 0;
    private int rowCounterMeasuringLine = 0;
    private int rowCounterMeasuringPoint = 0;
    private int rowCounterWaterLayer = 0;
    private int rowCounterCatchTools = 0;
    private int rowCounterWaterLayer_CatchTools = 0;

    String mon_name;
    String mon_site;
    String mon_river;
    String mon_time ;
    double mon_ltude;
    double mon_atude;
    private String mon_wht;
    double mon_tmpt;
    String fs_pst;
    double fs_dtb;
    private String wle_ly;
    private double wle_dep;
    private double wle_tempt;
    private double wle_wl;
    private double wle_vlty;
    private String net_name;
    private String net_nmd;
    private double net_nma;
    private double net_nmdp;
    private double net_nmv;
    private String sql, sql2;
    private String mon_id, sec_id, lin_id, pnt_id, wle_id;
    Statement st = null;
	@SuppressWarnings("finally")
	public void saveUser(){
		boolean flag = false;
		parseExcelUtil peu = new  parseExcelUtil();
		CatchTools dz= new CatchTools();
		int count = peu.getListDatas().size()+1;
		for (int i = 0; i < peu.getListDatas().size(); i++) {
			request.getSession().setAttribute("massage", "共有"+count+"条数据,已导入数据库"+i+"条,请等待操作,不要关闭浏览器");
			Map excelCol = (Map)peu.getListDatas().get(i);
			try {					
				BeanUtils.populate(dz, excelCol);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			mon_name = dz.getMon_name();
			mon_site = dz.getMon_site();
			mon_river = dz.getMon_river();
			mon_time = dz.getMon_time();
            mon_ltude = dz.getMon_ltude();
            mon_atude = dz.getMon_atude();
            mon_wht = dz.getMon_wth();
            mon_tmpt = dz.getMon_tmpt();
            fs_pst = dz.getFs_pst();
            fs_dtb = dz.getFs_dtb();
            wle_ly = dz.getWle_ly();
            wle_dep = dz.getWle_dep();
            wle_tempt = dz.getWle_tempt();
            wle_wl = dz.getWle_wl();
            wle_vlty = dz.getWle_vlty();
            net_name = dz.getNet_name();
            net_nmd = dz.getNet_nmd();
            net_nma = dz.getNet_nma();
            net_nmdp = dz.getNet_nmdp();
            net_nmv = dz.getNet_nmv();

			System.out.println(mon_name+mon_site+mon_river+mon_time+mon_ltude+mon_atude+mon_tmpt);
			String[] ms_phs=mon_time.split("\\ ");
			String[] ymd=ms_phs[0].split("-");	
		    String[] time = ms_phs[1].split(":");
		    time[2] = time[2].substring(0, 2);
			flag=false;
			mon_id="MON"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
            sql = "select * from MonitoringSite where InverstigationID='" + mon_id + "';";
            sql2 = "select * from  MonitoringSite where InverstigationID='" + mon_id + "' and Site='" + mon_site + "' and Investigator='" + mon_name + "';";
			try{
			    while(!flag){
			        flag = insertNewMoniteringSite(time, ymd);
			    }
                flag=false;
                sec_id="SEC"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
                sql = "select * from FractureSurface where ID='"+sec_id+"';";
                sql2 = "select * from FractureSurface where ID='"+sec_id+"' and Position='"+fs_pst+"';";
                while(!flag){
                    flag = insertNewFractureSurface(time, ymd);
                }
                flag=false;
                lin_id="LIN"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
                sql = "select * from MeasuringLine where ID='"+lin_id+"';";
                sql2 = "select * from MeasuringLine where ID='"+lin_id+"' and StartLongitude='"+mon_ltude+"' and StartLatitude='"+mon_atude+"';";
			    while(!flag){
                    flag = insertNewMoniteringLine(time, ymd);
			    }
                flag=false;
                pnt_id="PNT"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
                sql = "select * from MeasuringPoint where ID='"+pnt_id+"';";
                sql2 = "select * from MeasuringPoint where ID='"+pnt_id+"' and Longitude='"+mon_ltude+"' and Latitude='"+mon_atude+"';";
			    while(!flag){
                    flag = insertNewMoniteringPoint(time, ymd);
			    }

                flag=false;
                wle_id="WLE"+ ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
                sql = "select * from WaterLayer where ID='"+wle_id+"';";
                sql2 = "select * from WaterLayer where ID='"+wle_id+"' and Layer='"+wle_ly+"';";
			    while(!flag){
                    flag = insertNewWaterLayer(time, ymd);
			    }
			}catch (Exception e) {
				e.printStackTrace();
	        }
        }
        LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "MoniteringSite", rowCounterMonitoringSite);
		LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "FractureSurface", rowCounterFractureSurface);
		LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "MoniteringLine", rowCounterMeasuringLine);
		LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "MoniteringPoint",rowCounterMeasuringPoint);
		LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "WaterLayer", rowCounterWaterLayer);
		LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "CatchTools", rowCounterCatchTools);
		LogTool.insertIntoDataLog(LogTool.INSERT_CODE, "WaterLayer_CatchTools", rowCounterWaterLayer_CatchTools);
        request.getSession().setAttribute("massage", "导入进程结束");
    }
	/**
	 * 关闭资源
	 * 0
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private String decodeId(int hh, int mm, int ss, String [] time, String[] ymd) {
        if(ss/60==1){
            mm++;
            if(mm/60==1){
                hh++;
            }
        }
        ss=ss%60;
        mm=mm%60;
        hh=hh%24;
        time[0] =Integer.toString(hh);

        if(time[0].length()==1)
            time[0]="0"+time[0];
        time[1] =Integer.toString(mm);

        if(time[1].length()==1)
            time[1]="0"+time[1];
        time[2] = Integer.toString(ss);

        if(time[2].length()==1)
            time[2]="0"+time[2];
        return ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
    }

    private boolean insertNewMoniteringSite(String[] time, String[] ymd) throws SQLException {
	    boolean flag = false;
        ResultSet rs2 = db.executeQuery(sql2);
        if(rs2.next()){
            System.out.println(rs2.getString(1));
            if(rs2.getString(1)!=null){
                System.out.println("监测点已经存在");
                System.out.println(rs2.getString(1));
                flag=true;
            }
        } else{
            flag=false;
        }

        if(!flag){
            System.out.println("新的监测点");
            ResultSet rs = db.executeQuery(sql);
            System.out.println("rs="+rs);
            if(rs.next()){
                System.out.println("rs.getString(1)="+rs.getString(1));
                System.out.println("为新的监测点设置编号");
                System.out.println("rs.getString(1)="+rs.getString(1));
                int hh=Integer.parseInt(time[0]);
                int mm=Integer.parseInt(time[1]);
                int ss=Integer.parseInt(time[2]);
                ss++;
                mon_id ="MON"+ decodeId(hh, mm, ss, time, ymd);
            } else{
                flag=true;
                String sql3  = "insert into MonitoringSite values('"+mon_id+"','长江水产研究所','"+mon_name+"','"+mon_time+"','"+mon_site+"','"+mon_river+"',null,null,null,'"+mon_ltude+"','"+mon_atude+"','"+mon_ltude+"','"+mon_atude+"','"+mon_wht+"','"+mon_tmpt+"','"+account.getID()+"');";
                try{
                    db.executeUpdate(sql3);
                    rowCounterMonitoringSite ++;
                    System.out.println("导入一条数据");
                }catch (Exception e) {
                    System.out.println("llllll");
                    e.printStackTrace();
                }
            }
        }
        sql = "select * from MonitoringSite where InverstigationID='"+mon_id+"';";
        sql2 ="select * from  MonitoringSite where InverstigationID='"+mon_id+"' and Site='"+mon_site+"' and Investigator='"+mon_name+"';";
        return flag;
    }

    private boolean insertNewFractureSurface(String[] time, String[] ymd) throws SQLException {
	    boolean flag = false;
	    ResultSet rs2 = db.executeQuery(sql2);
        if(rs2.next()){
            System.out.println(rs2.getString(1));
            if(rs2.getString(1)!=null){
                System.out.println("断面已经存在");
                System.out.println(rs2.getString(1));
                flag=true;
            }
        } else{
            flag=false;
        }
        if(!flag){
            System.out.println("新的断面");
            ResultSet rs = db.executeQuery(sql);
            System.out.println("rs="+rs);
            if(rs.next()){
                int hh=Integer.parseInt(time[0]);
                int mm=Integer.parseInt(time[1]);
                int ss=Integer.parseInt(time[2]);
                ss++;
                sec_id ="SEC"+ decodeId(hh, mm, ss, time, ymd);
            } else{
                flag=true;
                String sql3  = "insert into FractureSurface values('"+sec_id+"','"+fs_pst+"','"+fs_dtb+"','"+mon_id+"')";
                try{
                    db.executeUpdate(sql3);
                    rowCounterFractureSurface++;
                }catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        sql = "select * from FractureSurface where ID='"+sec_id+"';";
        sql2 = "select * from FractureSurface where ID='"+sec_id+"' and Position='"+fs_pst+"';";
        return flag;
	}

	private boolean insertNewMoniteringLine(String[] time, String[] ymd) throws SQLException {
	    boolean flag = false;
        ResultSet rs2 = db.executeQuery(sql2);
        if(rs2.next()){
            System.out.println(rs2.getString(1));
            System.out.println("测线已经存在");
            System.out.println(rs2.getString(1));
            flag=true;
        }
        else{
            flag=false;
        }
        if(!flag){
            System.out.println("新的测线");
            ResultSet rs = db.executeQuery(sql);
            System.out.println("rs="+rs);
            if(rs.next()){
                int hh=Integer.parseInt(time[0]);
                int mm=Integer.parseInt(time[1]);
                int ss=Integer.parseInt(time[2]);
                ss++;
                lin_id ="LIN"+ decodeId(hh, mm, ss, time, ymd);
            }
            else{
                flag=true;
                String sql3  = "insert into MeasuringLine values('"+lin_id+"','"+mon_ltude+"','"+mon_atude+"',null,null,'"+sec_id+"')";
                try{
                    db.executeUpdate(sql3);
                    rowCounterMeasuringLine++;
                }catch (Exception e) {
                    e.printStackTrace();
                }

            }
            sql = "select * from MeasuringLine where ID='"+lin_id+"';";
            sql2 = "select * from MeasuringLine where ID='"+lin_id+"' and StartLongitude='"+mon_ltude+"' and StartLatitude='"+mon_atude+"';";
        }
        return flag;
    }

    private boolean insertNewMoniteringPoint(String[] time, String[] ymd) throws SQLException {
	    boolean flag = false;
        ResultSet rs2 = db.executeQuery(sql2);
        if(rs2.next()){
            System.out.println(rs2.getString(1));
            System.out.println("测点已经存在");
            System.out.println(rs2.getString(1));
            flag=true;
        }
        else{
            flag=false;
        }
        if(!flag){
            System.out.println("新的测点");
            ResultSet rs = db.executeQuery(sql);
            System.out.println("rs="+rs);
            if(rs.next()){
                int hh=Integer.parseInt(time[0]);
                int mm=Integer.parseInt(time[1]);
                int ss=Integer.parseInt(time[2]);
                ss++;
                pnt_id ="PNT"+ decodeId(hh, mm, ss, time, ymd);
            }
            else{
                flag=true;
                String sql3  = "insert into MeasuringPoint values('"+pnt_id+"','"+mon_ltude+"','"+mon_atude+"','"+lin_id+"')";
                try{
                    db.executeUpdate(sql3);
                    rowCounterMeasuringPoint ++;
                }catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }

            }

            sql = "select * from MeasuringPoint where ID='"+pnt_id+"';";
            sql2 = "select * from MeasuringPoint where ID='"+pnt_id+"' and Longitude='"+mon_ltude+"' and Latitude='"+mon_atude+"';";
        }
        return false;
    }

    private boolean insertNewWaterLayer(String[] time, String[] ymd) throws SQLException {
	    boolean flag = false;
        ResultSet rs2 = db.executeQuery(sql2);
        if(rs2.next()){
            System.out.println(rs2.getString(1));
            System.out.println("采样水层已经存在");
            System.out.println(rs2.getString(1));
            flag=true;
        }
        else{
            flag=false;
        }
        if(flag==false){
            System.out.println("新的采样水层");
            ResultSet rs = db.executeQuery(sql);
            System.out.println("rs="+rs);
            if(rs.next()){
                int hh=Integer.parseInt(time[0]);
                int mm=Integer.parseInt(time[1]);
                int ss=Integer.parseInt(time[2]);
                ss++;
                wle_id ="WLE"+ decodeId(hh, mm, ss, time, ymd);
            }
            else{
                flag=true;
                String sql3  = "insert into WaterLayer values('"+wle_id+"','"+wle_ly+"','"+wle_dep+"','"+wle_tempt+"','"+wle_wl+"','"+wle_vlty+"','"+pnt_id+"')";
                try{
                    System.out.println(sql3);
                    db.executeUpdate(sql3);
                    rowCounterWaterLayer++;
                }catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
                String net_id = "NET"+ymd[0]+ymd[1]+ymd[2]+time[0]+time[1]+time[2];
                String sql4  = "insert into CatchTools values('"+net_id+"','"+net_name+"',null,'"+net_nmd+"','"+net_nma+"','"+net_nmdp+"',null,null,'"+net_nmv+"')";
                try{
                    db.executeUpdate(sql4);
                    rowCounterCatchTools ++;
                }catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
                String sql5  = "insert into WaterLayer_CatchTools values('"+wle_id+"','"+net_id+"')";
                try{
                    db.executeUpdate(sql5);
                    rowCounterWaterLayer_CatchTools++;
                }catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
            }

            sql = "select * from WaterLayer where ID='"+wle_id+"';";
            sql2 = "select * from WaterLayer where ID='"+wle_id+"' and Layer='"+wle_ly+"';";
        }
        return flag;
    }
}
